ExcelVBAPartsCollection Home Excel Reference Manual DownLoad My Profile
Menu Back Next Links Excel Function Manual Myself My BBS


データ抽出・変換

　
フィルタオプションによるデータ抽出
数値と文字列としての数字の変換
文字列を指定した文字列に変換
文字列内のスペースの除去
文字列の置換




フィルタオプションによるデータ抽出


　フィルタオプション(AdvancedFilter メソッド)は、検索条件範囲に基づいて、リストにフィルタをかけます。抽出結果は、選択範囲内に表示するか、他の範囲にデータをコピーするかを選択できます。選択された範囲が単一のセルのときは、そのアクティブ セル領域が使われます。フィルタオプションの詳細は、ここをクリックしてください。
　
[ AdvancedFilter メソッドの構文 ]
　　expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
　
　expression は必ず指定します。フィルタを行うリスト範囲内の 1 つのセルを指定します。引数の内容は、下記のとおりです。

引　　　数 内　　　　　　　　　　　　　　　容
Action  必ず指定します。抽出結果の表示方法を指定します。
定　　　数 内　　　　　　　　　　　　　　　容
xlFilterInPlace 検索条件に一致する行だけを表示し、それ以外の行を非表示にします。
xlFilterCopy 検索条件に一致する行をワークシートの別の場所にコピーして抽出します。
コピー先セル範囲は、CopyToRange で指定します。 
CriteriaRange  省略可能です。検索条件範囲を指定します。省略すると、検索条件なしで抽出されます。
CopyToRange  省略可能です。引数 Action を xlFilterCopy に設定したときに、抽出された行のコピー先のセル範囲の左上隅となるセルを指定します。
Unique 省略可能です。検索条件に一致するレコードのうち、重複するレコードを無視するかとうかを指定します。
定　　　数 内　　　　　　　　　　　　　　　容
True  重複するレコードは無視して抽出されます。
False 重複するレコードも含めて、抽出されます。既定値。





　以下は、上表をもとに例題を作成しましたので、コードと参照してください。

1　単一条件によるデータ抽出

処理内容：リストから男性のデータのみ抽出します
Sub AdvancedFilter_1()
Dim Drange As Range,Crange As Range
Worksheets("Sheet1").Select
' 検索条件式の入力
Range("H1").Value = Range("B1").Value
Range("H2").Value = "男"
' フィルタの実行
With Worksheets("Sheet1")
Set Drange = .Range("A1").CurrentRegion
Set Crange = .Range("H1").CurrentRegion
End With
Range("A1").Select
Drange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Crange, Unique:=False
Set Drange =Nothing
Set Crange =Nothing
End Sub

2　AND 条件によるデータ抽出

処理内容：女性で年齢20歳以上40歳未満のデータを抽出します
Sub AdvancedFilter_2()
Dim Drange As Range,Crange As Range
Worksheets("Sheet1").Select
' 検索条件式の入力
Range("H1").Value = Range("B1").Value
Range("H2").Value = "女"
Range("I1:J1").Value = Range("E1").Value
Range("I2").Value = ">=20"
Range("J2").Value = "<40"
' フィルタの実行
With Worksheets("Sheet1")
Set Drange = .Range("A1").CurrentRegion
Set Crange = .Range("H1").CurrentRegion
End With
Range("A1").Select
Drange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Crange, Unique:=False
Set Drange =Nothing
Set Crange =Nothing
End Sub

3　OR 条件によるデータ抽出

処理内容：県名が愛知県または三重県のデータを抽出
Sub AdvancedFilter_3()
Dim Drange As Range,Crange As Range
Worksheets("Sheet1").Select
' 検索条件式の入力
Range("H1").Value = Range("D1").Value
Range("H2").Value = "愛知"
Range("H3").Value = "三重"
' フィルタの実行
With Worksheets("Sheet1")
Set Drange = .Range("A1").CurrentRegion
Set Crange = .Range("H1").CurrentRegion
End With
Range("A1").Select
Drange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Crange, Unique:=False
Set Drange =Nothing
Set Crange =Nothing
End Sub

4　検索条件を"Sheet3"に作成し抽出結果は"H1"を左上隅として表示

処理内容：県名が愛知県または三重県のデータを抽出
Sub AdvancedFilter_4()
Dim Drange As Range, Crange As Range
' 検索条件式の入力
With Worksheets("Sheet3")
.Range("A1").Value = Worksheets("Sheet1").Range("D1").Value
.Range("A2").Value = "愛知"
.Range("A3").Value = "三重"
Set Crange = .Range("A1").CurrentRegion
End With
' フィルタの実行
With Worksheets("Sheet1")
Set Drange = .Range("A1").CurrentRegion
.Range("A1").Select
Drange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Crange, _
CopyToRange:=.Range("H1"), Unique:=False
End With
Set Drange =Nothing
Set Crange =Nothing
End Sub

5　フィルタオプションを解除

処理内容：フィルタオプションを解除してすべてのデータを表示します
Sub AdvancedFilter_5()
Worksheets("Sheet1").Select
ActiveSheet.ShowAllData
End Sub




数値と文字列としての数字の変換


　文字列としての数字を数値に変換するには、Var 関数を使用します。また、文字列としての数値に 1 を乗じても数値に変換することができます。Var 関数の書式は、Val(文字列式)です。

　数値を文字列としての数字に変換するには、Str 関数を使用します。また、数値の前に" ' (アポストロフィ)" を付けても文字列に変換することができます。

1　数値と文字列の変換

処理内容：文字列としての数字を数値に変換(Val 関数使用)
Sub val_1()
Worksheets("Sheet1").Select
Cells(1, 1) = "'0123"
Cells(1, 2) = Val(Cells(1, 1))
End Sub

処理内容：文字列としての数字を数値に変換(1を乗じる)
Sub val_2()
Worksheets("Sheet1").Select
Cells(2, 1) = "'0123"
Cells(2, 2) = Cells(2, 1)*1
End Sub

処理内容：数値を文字列としての数字に変換( 「 ' アポストロフィ」 を数値の前に付ける)
Sub Str_1()
Worksheets("Sheet1").Select
Cells(4, 1) = 123
Cells(4, 2) = "'" & Cells(4, 1)
End Sub

処理内容：数値を文字列としての数字に変換(書式指定により先頭に 0 を付ける)
Sub Str_2()
Worksheets("Sheet1").Select
Cells(5, 1) = 123
Cells(5, 2).NumberFormat = "@"
Cells(5, 2) = Format(Str(Cells(5, 1)), "0000")
End Sub


文字列を指定した文字列に変換


1　UCase 関数、LCase 関数による大文字・小文字変換

 　UCase 関数は、英字の小文字をすべて大文字に変換し、LCase 関数は、英字の大文字をすべて小文字に変換します。

1　UCase 関数、LCase 関数による大文字・小文字の変換

処理内容：英字の小文字をすべて大文字に変換します
Sub UCase_1()
Worksheets("Sheet1").Select
Cells(1, 1) = "abc"
Cells(1, 2) = UCase(Cells(1, 1))
End Sub

処理内容：英字の大文字をすべて小文字に変換します
Sub LCase_2()
Worksheets("Sheet1").Select
Cells(2, 1) = "ABC"
Cells(2, 2) = LCase(Cells(2, 1))
End Sub




2　Format 関数による大文字・小文字変換

 　Format 関数により英字の大文字・小文字を変換することができます。

2　Format 関数による大文字・小文字の変換

処理内容：英字の小文字をすべて大文字に変換します
Sub Format_1()
Worksheets("Sheet1").Select
Cells(3, 1) = "abc"
Cells(3, 2) = Format(Cells(3, 1),">")
End Sub

処理内容：英字の大文字をすべて小文字に変換します
Sub Format_2()
Worksheets("Sheet1").Select
Cells(4, 1) = "abc"
Cells(4, 2) = Format(Cells(4, 1),"<")
End Sub




3　StrConv 関数による文字列変換

　文字列を指定した文字列形式に変換します。

 　[ StrConv 関数の構文 ]
　　StrConv(string, conversion)
　
　　string は必ず指定します。変換する文字列式を指定します。
　　引数 conversion の定数の内容は、下記のとおりです。

定　　　数  値  内　　　　　　　　　　　　　　　容
vbUpperCase  1 小文字を大文字に変換します。 
vbLowerCase  2 大文字を小文字に変換します。 
vbProperCase 3 各単語の先頭の文字を大文字に変換します。 
vbWide 4 文字列内の半角文字 (1 バイト) を全角文字 (2 バイト) に変換します。 
vbNarrow 8 文字列内の全角文字 (2 バイト) を半角文字 (1 バイト) に変換します。 
vbKatakana 16 文字列内のひらがなをカタカナに変換します。 
vbHiragana 32 文字列内のカタカナをひらがなに変換します。 
vbUnicode 64 システムの既定のコード ページを使って文字列を Unicode に変換します。Macintosh. では使用できません) 
vbFromUnicode 128 文字列を Unicode からシステムの既定のコード ページに変換します。Macintosh. では使用できません) 
通常、定数は互いに矛盾しない限り、組み合わせて指定できます。たとえば、定数 vbUpperCase と vbWide を組み合わせることはできます。しかし、定数 vbUnicode と vbFromUnicode は指定内容が矛盾するため、組み合わせることはできません。また、不適切な場所で定数 vbWide、vbNarrow、vbKatakana、および vbHiragana を指定した場合は、実行時エラーが発生します。




3　StrConv 関数による文字列の変換

処理内容：英字の小文字を大文字に変換します
Sub StrConv_1()
Worksheets("Sheet1").Select
Cells(2, 1) = "abc"
Cells(2, 2) = StrConv(Cells(2, 1), vbUpperCase)　　' 結果　ABC
End Sub

処理内容：英字の大文字を小文字に変換します
Sub StrConv_2()
Worksheets("Sheet1").Select
Cells(1, 1) = "ABC"
Cells(1, 2) = StrConv(Cells(1, 1), vbLowerCase)　　' 結果　abc
End Sub

処理内容：英字の先頭文字を小文字から大文字に変換変換します
Sub StrConv_3()
Worksheets("Sheet1").Select
Cells(3, 1) = "abc"
Cells(3, 2) = StrConv(Cells(3, 1), vbProperCase)　　' 結果　Abc
End Sub

処理内容：半角文字を全角文字に変換します
Sub StrConv_4()
Worksheets("Sheet1").Select
Cells(4, 1) = "ｱｲｳ"
Cells(4, 2) = StrConv(Cells(4, 1) , vbWide)　　' 結果　アイウ
End Sub

処理内容：全角文字を半角文字に変換します
Sub StrConv_5()
Worksheets("Sheet1").Select
Cells(5, 1) = "アイウ"
Cells(5, 2) = StrConv(Cells(5, 1), vbNarrow)　　' 結果　ｱｲｳ
End Sub

処理内容：ひらがなをカタカナに変換します
Sub StrConv_6()
Worksheets("Sheet1").Select
Cells(6, 1) = "あいう"
Cells(6, 2) = StrConv(Cells(6, 1), vbKatakana)　　' 結果　アイウ
End Sub

処理内容：カタカナをひらがなに変換します
Sub StrConv_7()
Worksheets("Sheet1").Select
Cells(7, 1) = "アイウ"
Cells(7, 2) = StrConv(Cells(7, 1), vbHiragana)　　' 結果　あいう
End Sub

処理内容：システムの既定のコード ページを使って文字列を Unicode に変換します
Sub StrConv_8()
Worksheets("Sheet1").Select
Cells(8, 1) = "abc"
Cells(8, 2) = StrConv(Cells(8, 1), vbUnicode)　　' 結果　a
End Sub

処理内容：文字列を Unicode からシステムの既定のコード ページに変換します
Sub StrConve_9()
Worksheets("Sheet1").Select
Cells(9, 1) = "ABC"
Cells(9, 2) = StrConv(Cells(8, 1),vbFromUnicode)　　' 結果　扡c
End Sub

処理内容：文字列を全角から半角にして、先頭文字を大文字にします
Sub StrConv_10()
Worksheets("Sheet1").Select
Cells(13, 1) = "ＶＩＳＵＡＬＢＡＳＩＣ"
Cells(13, 2) = StrConv(Cells(13, 1), vbNarrow + vbProperCase)　　' 結果　Visualbasic
End Sub




文字列内のスペースの除去


　文字列に含まれているスペースを除去する関数として、Trim 関数があります。除去するスペースの位置によってそれぞれ異なった関数があります。

　文字列中の全てのスペースを除去するには、Replace 関数を使用します。(次項で説明します。)

関数名

 内　　　　　　　　　　　　　　　容

LTrim

 指定した文字列から先頭のスペースを除去します
RTrim 指定した文字列から末尾のスペース除去します
Trim 指定した文字列から先頭と末尾の両方のスペース除去します
TRIM ワークシート関数です。文字列に複数のスペースが連続して含まれている場合、単語間のスペースを 1 つずつ残して、不要なスペースをすべて除去します。

スペースの除去

処理内容：文字列の左側のスペースを除去します
Sub Trim_1()
Worksheets("Sheet1").Select
Cells(1, 1) = "　　　ア　イ　ウ "
Cells(1, 2) = LTrim(Cells(1, 1))
End Sub

処理内容：文字列の右側のスペースを除去します
Sub Trim_2()
Worksheets("Sheet1").Select
Cells(2, 1) = "　　　ア　イ　ウ 　　"
Cells(2, 2) = RTrim(Cells(2, 1))
End Sub

処理内容：文字列の左右のスペースを除去します
Sub Trim_3()
Worksheets("Sheet1").Select
Cells(3, 1) = "　　　ア　イ　ウ　 "
Cells(3, 2) = Trim(Cells(3, 1))
End Sub

処理内容：文字列の左右のスペースを除去します(ワークシート関数)
Sub Trim_4()
Worksheets("Sheet1").Select
Cells(4, 1) = "　　　ア　イ　ウ　 "
Cells(4, 2) = Application.WorksheetFunction.Trim(Cells(4, 1))
End Sub




文字列の置換


　指定された文字列の一部を、別の文字列で置換します。置換を行うには、Replace 関数を用いる方法と、Characters プロパティと Text プロパティを併用する方法があります。

1　Replace 関数による置換
Replace 関数は、指定された文字列の一部を、別の文字列で指定された回数分で置換した文字列を返します。構文は次のとおりです。

[ Replace 関数の構文 ]
　　Replace(expression, find, replace[, start[, count[, compare]]])

　Replace 関数の引数と内容は次のとおりです。

引　　数 内　　　　　　　　　　　　　　　容
expression 必ず指定します。置換する文字列を含む文字列式 を指定します。 
find 必ず指定します。検索する文字列を指定します。 
replace 必ず指定します。置換する文字列を指定します。 
start 省略可能です。文字列の検索開始位置を指定します。この引数を省略すると、1 が使用されます。
count 省略可能です。置換する文字列数を指定します。この引数を省略すると、既定値の -1 が使用され、すべての候補が置換されます。 
compare 省略可能です。文字列式を評価するときに使用する文字列比較のモードを表す数値を指定します。




Replace 関数による置換

処理内容：文字列を別の文字列に変換(DEFをXYZに変換)します
Sub Replace_1()
Worksheets("Sheet1").Select
Cells(1, 1).Value = "ABCDEF"
Cells(1, 1).Replace What:="DEF", Replacement:="XYZ"
End Sub

処理内容：文字列内（前後を含む）のスペースを削除
Sub Replace_2()
Worksheets("Sheet1").Select
Cells(2, 1).Value = " A　B　C D"
Cells(2, 1).Replace What:="　", Replacement:=""　　' 全角スペース削除
Cells(2, 1).Replace What:=" ", Replacement:=""　　' 半角スペース削除
End Sub

2　CharactersとText プロパティによる置換
　Characters プロパティは、テキスト内の文字範囲を指定します。構文は下記のとおりです。
　[ Characters プロパティの構文 ]
　　expression.Characters(Start, Length)

　　espression　セル参照を表す式を指定します。
　　Start           先頭文字を指定します。省略すると先頭文字を指定したこととなります。
　　Length        文字数を指定します。省略すると先頭文字の残りの部分の文字数となります。

　Text プロパティの値に、置換する文字列を指定します。

CharactersとText プロパティによる置換

処理内容：2文字目から3文字分を"かきく"に変更します
Sub Text_1()
Worksheets("Sheet1").Select
Cells(3, 1).Value = "あいうえお"
Cells(3, 1).Characters(2, 3).Text = "かきく"
End Sub

処理内容：3文字目以降を"かきく"に変更します
Sub Text_2()
Worksheets("Sheet1").Select
Cells(4, 1).Value = "あいうえお"
Cells(4, 1).Characters(3).Text = "かきく"
End Sub




楽天モバイル[UNLIMITが今なら1円] ECナビでポインと Yahoo 楽天 LINEがデータ消費ゼロで月額500円〜！


無料ホームページ 無料のクレジットカード 海外格安航空券 解約手数料０円【あしたでんき】 海外旅行保険が無料！ 海外ホテル